﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using VOL;
using DAL;

namespace BLL
{
    public class HangHoaBL
    {
        private DataAccess dataAccess;
        public HangHoaBL()
        {
            dataAccess = new DataAccess();
        }

        public DataTable GetData()
        {
            string strSql = "SELECT * FROM tbl_HangHoa ORDER BY MaHangHoa DESC ";
            return dataAccess.GetData(strSql);
        }

        public DataTable GetData(HangHoa obj)
        {
            string strWhere = " WHERE 1=1 ";
            List<SqlParameter> lstParam = new List<SqlParameter>();
            if (obj.MaNhaSanXuat > 0)
            {
                lstParam.Add(new SqlParameter("@MaNhaSanXuat", SqlDbType.Int) { Value = obj.MaNhaSanXuat });
                strWhere += " AND MaNhaSanXuat = @MaNhaSanXuat ";
            }
            if (obj.MaLoaiHangHoa > 0)
            {
                lstParam.Add(new SqlParameter("@MaLoaiHangHoa", SqlDbType.Int) { Value = obj.MaLoaiHangHoa });
                strWhere += " AND MaLoaiHangHoa = @MaLoaiHangHoa ";
            }
            if (obj.MaChungLoai > 0)
            {
                lstParam.Add(new SqlParameter("@MaChungLoai", SqlDbType.Int) { Value = obj.MaChungLoai });
                strWhere += " AND MaChungLoai = @MaChungLoai ";
            }
            string strSql = "SELECT * FROM tbl_HangHoa " + strWhere + " ORDER BY MaHangHoa DESC ";
            return dataAccess.GetData(strSql,lstParam);
        }

        public DataTable GetDataGrip()
        {
            string strSql = "SELECT tbl_HangHoa.MaHangHoa,tbl_HangHoa.TenHangHoa, tbl_HangHoa.HinhAnh, tbl_LoaiHangHoa.TenLoaiHangHoa, tbl_NhaSanXuat.TenNhaSanXuat, tbl_HangHoa.DonGia, tbl_ChungLoai.TenChungLoai FROM  tbl_ChungLoai INNER JOIN tbl_HangHoa ON tbl_ChungLoai.MaChungLoai = tbl_HangHoa.MaChungLoai INNER JOIN tbl_LoaiHangHoa ON tbl_HangHoa.MaLoaiHangHoa = tbl_LoaiHangHoa.MaLoaiHangHoa INNER JOIN tbl_NhaSanXuat ON tbl_HangHoa.MaNhaSanXuat = tbl_NhaSanXuat.MaNhaSanXuat ORDER BY MaHangHoa DESC ";
            return dataAccess.GetData(strSql);
        }

        public DataTable searchTenHangHoa(string tenTimKiem)
        {
            string strSql = "SELECT tbl_HangHoa.MaHangHoa,tbl_HangHoa.TenHangHoa, tbl_HangHoa.HinhAnh, tbl_LoaiHangHoa.TenLoaiHangHoa, tbl_NhaSanXuat.TenNhaSanXuat, tbl_HangHoa.DonGia, tbl_ChungLoai.TenChungLoai FROM  tbl_ChungLoai INNER JOIN tbl_HangHoa ON tbl_ChungLoai.MaChungLoai = tbl_HangHoa.MaChungLoai INNER JOIN tbl_LoaiHangHoa ON tbl_HangHoa.MaLoaiHangHoa = tbl_LoaiHangHoa.MaLoaiHangHoa INNER JOIN tbl_NhaSanXuat ON tbl_HangHoa.MaNhaSanXuat = tbl_NhaSanXuat.MaNhaSanXuat WHERE TenHangHoa like '%" + tenTimKiem + "%' ORDER BY MaHangHoa DESC ";
            return dataAccess.GetData(strSql);
        }

        public DataTable searchMaHangHoa(int maTimKiem)
        {
            string strSql = "";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaHangHoa", SqlDbType.Int) { Value = maTimKiem };
            strSql = "SELECT tbl_HangHoa.MaHangHoa,tbl_HangHoa.TenHangHoa, tbl_HangHoa.HinhAnh, tbl_LoaiHangHoa.TenLoaiHangHoa, tbl_NhaSanXuat.TenNhaSanXuat, tbl_HangHoa.DonGia, tbl_ChungLoai.TenChungLoai FROM  tbl_ChungLoai INNER JOIN tbl_HangHoa ON tbl_ChungLoai.MaChungLoai = tbl_HangHoa.MaChungLoai INNER JOIN tbl_LoaiHangHoa ON tbl_HangHoa.MaLoaiHangHoa = tbl_LoaiHangHoa.MaLoaiHangHoa INNER JOIN tbl_NhaSanXuat ON tbl_HangHoa.MaNhaSanXuat = tbl_NhaSanXuat.MaNhaSanXuat WHERE MaHangHoa = @MaHangHoa ORDER BY MaHangHoa DESC ";
            return dataAccess.GetData(strSql, param);
        }

        public DataTable searchMaChungLoai(int maTimKiem)
        {
            string strSql = "";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaChungLoai", SqlDbType.Int) { Value = maTimKiem };
            strSql = "SELECT tbl_HangHoa.MaHangHoa,tbl_HangHoa.TenHangHoa, tbl_HangHoa.HinhAnh, tbl_LoaiHangHoa.TenLoaiHangHoa, tbl_NhaSanXuat.TenNhaSanXuat, tbl_HangHoa.DonGia, tbl_ChungLoai.TenChungLoai FROM  tbl_ChungLoai INNER JOIN tbl_HangHoa ON tbl_ChungLoai.MaChungLoai = tbl_HangHoa.MaChungLoai INNER JOIN tbl_LoaiHangHoa ON tbl_HangHoa.MaLoaiHangHoa = tbl_LoaiHangHoa.MaLoaiHangHoa INNER JOIN tbl_NhaSanXuat ON tbl_HangHoa.MaNhaSanXuat = tbl_NhaSanXuat.MaNhaSanXuat WHERE tbl_ChungLoai.MaChungLoai = @MaChungLoai ORDER BY MaHangHoa DESC ";
            return dataAccess.GetData(strSql, param);
        }

        public DataTable searchNhaSanXuat(int maTimKiem)
        {
            string strSql = "";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaNhaSanXuat", SqlDbType.Int) { Value = maTimKiem };
            strSql = "SELECT tbl_HangHoa.MaHangHoa,tbl_HangHoa.TenHangHoa, tbl_HangHoa.HinhAnh, tbl_LoaiHangHoa.TenLoaiHangHoa, tbl_NhaSanXuat.TenNhaSanXuat, tbl_HangHoa.DonGia, tbl_ChungLoai.TenChungLoai FROM  tbl_ChungLoai INNER JOIN tbl_HangHoa ON tbl_ChungLoai.MaChungLoai = tbl_HangHoa.MaChungLoai INNER JOIN tbl_LoaiHangHoa ON tbl_HangHoa.MaLoaiHangHoa = tbl_LoaiHangHoa.MaLoaiHangHoa INNER JOIN tbl_NhaSanXuat ON tbl_HangHoa.MaNhaSanXuat = tbl_NhaSanXuat.MaNhaSanXuat WHERE tbl_NhaSanXuat.MaNhaSanXuat = @MaNhaSanXuat ORDER BY MaHangHoa DESC ";
            return dataAccess.GetData(strSql, param);
        }

        public DataTable searchLoaiHangHoa(int maTimKiem)
        {
            string strSql = "";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaLoaiHangHoa", SqlDbType.Int) { Value = maTimKiem };
            strSql = "SELECT tbl_HangHoa.MaHangHoa,tbl_HangHoa.TenHangHoa, tbl_HangHoa.HinhAnh, tbl_LoaiHangHoa.TenLoaiHangHoa, tbl_NhaSanXuat.TenNhaSanXuat, tbl_HangHoa.DonGia, tbl_ChungLoai.TenChungLoai FROM  tbl_ChungLoai INNER JOIN tbl_HangHoa ON tbl_ChungLoai.MaChungLoai = tbl_HangHoa.MaChungLoai INNER JOIN tbl_LoaiHangHoa ON tbl_HangHoa.MaLoaiHangHoa = tbl_LoaiHangHoa.MaLoaiHangHoa INNER JOIN tbl_NhaSanXuat ON tbl_HangHoa.MaNhaSanXuat = tbl_NhaSanXuat.MaNhaSanXuat WHERE tbl_LoaiHangHoa.MaLoaiHangHoa = @MaLoaiHangHoa ORDER BY MaHangHoa DESC ";
            return dataAccess.GetData(strSql, param);
        }

        //Thêm
        public bool insertHangHoa(HangHoa obj)
        {
            SqlParameter[] param = new SqlParameter[11];
            param[0] = new SqlParameter("@MaLoaiHangHoa", SqlDbType.Int) { Value = obj.MaLoaiHangHoa };
            param[1] = new SqlParameter("@MaChungLoai", SqlDbType.Int) { Value = obj.MaChungLoai };
            param[2] = new SqlParameter("@MaNhaSanXuat", SqlDbType.Int) { Value = obj.MaNhaSanXuat };
            param[3] = new SqlParameter("@TenHangHoa", SqlDbType.NVarChar) { Value = obj.TenHangHoa };
            param[4] = new SqlParameter("@KichCo", SqlDbType.Int) { Value = obj.KichCo };
            param[5] = new SqlParameter("@SoLuongHienCo", SqlDbType.Int) { Value = obj.SoLuongHienCo };
            param[6] = new SqlParameter("@DonGia", SqlDbType.Float) { Value = obj.DonGia };
            param[7] = new SqlParameter("@KhuyenMai", SqlDbType.Int) { Value = obj.KhuyenMai };
            param[8] = new SqlParameter("@HinhAnh", SqlDbType.NVarChar) { Value = obj.HinhAnh };
            param[9] = new SqlParameter("@NgayNhap", SqlDbType.DateTime) { Value = obj.NgayNhap };
            param[10] = new SqlParameter("@ChiTiet", SqlDbType.NVarChar) { Value = obj.ChiTiet };

            string strSql = "INSERT INTO tbl_HangHoa(MaLoaiHangHoa,MaChungLoai,MaNhaSanXuat,TenHangHoa,KichCo,SoLuongHienCo,DonGia,KhuyenMai,HinhAnh,NgayNhap,ChiTiet ) VALUES (@MaLoaiHangHoa,@MaChungLoai,@MaNhaSanXuat,@TenHangHoa,@KichCo,@SoLuongHienCo,@DonGia,@KhuyenMai,@HinhAnh,@NgayNhap,@ChiTiet)";
            dataAccess.Execute(strSql, param);

            return true;
        }

        ////Xóa
        public bool deleteHangHoa(HangHoa obj)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaHangHoa", SqlDbType.Int) { Value = obj.MaHangHoa };

            string strSql = "DELETE FROM tbl_HangHoa WHERE MaHangHoa = @MaHangHoa";
            dataAccess.Execute(strSql, param);
            return true;
        }

        //Xóa theo mã nhà sản xuất
        public bool deleteMaNhaSanXuat(int maNhaSanXuat)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaNhaSanXuat", SqlDbType.Int) { Value = maNhaSanXuat };

            string strSql = "DELETE FROM tbl_HangHoa WHERE MaNhaSanXuat = @MaNhaSanXuat";
            dataAccess.Execute(strSql, param);
            return true;

        }

        ////Sửa
        public bool updateHangHoa(HangHoa obj)
        {
            SqlParameter[] param = new SqlParameter[12];
            param[0] = new SqlParameter("@MaLoaiHangHoa", SqlDbType.Int) { Value = obj.MaLoaiHangHoa };
            param[1] = new SqlParameter("@MaChungLoai", SqlDbType.Int) { Value = obj.MaChungLoai };
            param[2] = new SqlParameter("@MaNhaSanXuat", SqlDbType.Int) { Value = obj.MaNhaSanXuat };
            param[3] = new SqlParameter("@TenHangHoa", SqlDbType.NVarChar) { Value = obj.TenHangHoa };
            param[4] = new SqlParameter("@KichCo", SqlDbType.Int) { Value = obj.KichCo };
            param[5] = new SqlParameter("@SoLuongHienCo", SqlDbType.Int) { Value = obj.SoLuongHienCo };
            param[6] = new SqlParameter("@DonGia", SqlDbType.Float) { Value = obj.DonGia };
            param[7] = new SqlParameter("@KhuyenMai", SqlDbType.Int) { Value = obj.KhuyenMai };
            param[8] = new SqlParameter("@HinhAnh", SqlDbType.NVarChar) { Value = obj.HinhAnh };
            param[9] = new SqlParameter("@NgayNhap", SqlDbType.DateTime) { Value = obj.NgayNhap };
            param[10] = new SqlParameter("@ChiTiet", SqlDbType.NVarChar) { Value = obj.ChiTiet };
            param[11] = new SqlParameter("@MaHangHoa", SqlDbType.Int) { Value = obj.MaHangHoa };

            string strSql = "UPDATE tbl_HangHoa SET " +
                            "MaLoaiHangHoa = @MaLoaiHangHoa, " +
                            "MaChungLoai = @MaChungLoai, " +
                            "MaNhaSanXuat = @MaNhaSanXuat, " +
                            "TenHangHoa = @TenHangHoa, " +
                            "KichCo = @KichCo, " +
                            "SoLuongHienCo = @SoLuongHienCo, " +
                            "DonGia = @DonGia, " +
                            "KhuyenMai = @KhuyenMai, " +
                            "HinhAnh = @HinhAnh, " +
                            "NgayNhap = @NgayNhap, " +
                            "ChiTiet = @ChiTiet " +
                            "WHERE MaHangHoa = @MaHangHoa";
            dataAccess.Execute(strSql, param);
            return true;
        }

        ////Tìm theo Mã Hàng Hóa
        public DataTable searchIDHangHoa(int maHangHoa)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaHangHoa", SqlDbType.Int) { Value = maHangHoa };

            string strSql = "SELECT * FROM tbl_HangHoa WHERE MaHangHoa = @MaHangHoa";
            return dataAccess.GetData(strSql, param);
        }

        public DataTable chiTiet(int MaHang)
        {

            string strSql = "SELECT tbl_HangHoa.*, tbl_LoaiHangHoa.TenLoaiHangHoa, tbl_NhaSanXuat.TenNhaSanXuat FROM tbl_HangHoa INNER JOIN tbl_LoaiHangHoa ON tbl_HangHoa.MaLoaiHangHoa = tbl_LoaiHangHoa.MaLoaiHangHoa INNER JOIN tbl_NhaSanXuat ON tbl_HangHoa.MaNhaSanXuat = tbl_NhaSanXuat.MaNhaSanXuat WHERE MaHangHoa = " +MaHang.ToString();
            return dataAccess.GetData(strSql);
        }
        //còn nữa
    }
}
